﻿using System;
using System.Linq;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace RESP_IIWaterAssessment
{
    public partial class ExecutivePowersReport : Form
    {
        public ExecutivePowersReport()
        {
            InitializeComponent();
        }

        int regID = 0;

        private void ExecutivePowersReport_Load(object sender, EventArgs e)
        {
            cbRegions.SelectedIndexChanged -= cbRegions_SelectedIndexChanged;
            cbRegions.DataSource = UtilClass.FillCBRegions();
            cbRegions.DisplayMember = "Name";
            cbRegions.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegions.SelectedIndex = -1;
                cbRegions.Text = string.Empty;
                cbRegions.SelectedIndexChanged += new EventHandler(cbRegions_SelectedIndexChanged);
            }
            else
            {
               
                cbRegions.Enabled = false;
                regID = InformationClass.regionID;
                cbRegions.SelectedIndexChanged += new EventHandler(cbRegions_SelectedIndexChanged);
                cbRegions.SelectedValue = regID;
                cbRegions_SelectedIndexChanged(cbRegions, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;

            }
        }

        private void cbRegions_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbAVP.DataSource = DBClass.AVP
                .Where(wh => wh.RegionID == Convert.ToInt32(cbRegions.SelectedValue))
                .Select(se => new { ID = se.ID, Name = se.Name }).OrderBy(o => o.Name).ToList();
            cbAVP.DisplayMember = "Name";
            cbAVP.ValueMember = "ID";
            cbAVP.SelectedIndex = -1;
            cbAVP.Text = string.Empty;
        }

        private void buttonReport_Click(object sender, EventArgs e)
        {
            ShowReport();
            for (int i = 0; i < dgvReport.RowCount; i++)
            {
                dgvReport.Rows[i].HeaderCell.Value = (i+1).ToString();
            }
        }

        private void ShowReport()
        {

            int? regID = null, avpID = null;

            if (cbAVP.SelectedIndex >= 0)
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
            else if (cbRegions.SelectedIndex >= 0)
                regID = Convert.ToInt32(cbRegions.SelectedValue);



            //var report = DBClass.AVP.Join(DBClass.AVPState, avp => avp.ID, avpstate => avpstate.AVPID, (avp, avpstate) => new { Name = avp.Name, Staff = avpstate.StaffQuantity, Count = DBClass.Members.Count(c => c.AVPID == avp.ID) }); 
            dgvReport.DataSource = DBClass.RESPContext.ExecutivePowers(regID, avpID, DateTime.Now.Year).ToList();
            dgvReport.ReadOnly = true;

            dgvReport.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvReport.AutoResizeColumns();

            if (InformationClass.Lang)
            {
                dgvReport.Columns[0].HeaderText = "АВП";
                dgvReport.Columns[1].HeaderText = "Количество по штату";
                dgvReport.Columns[2].HeaderText = "Количество по Факту";
            }
            else
            {
                dgvReport.Columns[0].HeaderText = "СИУ номи";
                dgvReport.Columns[1].HeaderText = "Штатдаги ходимлар сони";
                dgvReport.Columns[2].HeaderText = "Амалда ишлаётган ходимлар сони";
            }
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;
        //Workbooks excelappworkbooks;

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvReport.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting);
                GetReport();
                
                excelworksheet.Select(1);
                excelapp.Visible = true;
            }
        }

        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();
            excelapp.SheetsInNewWorkbook = 1;

            if (InformationClass.Lang)
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\r6.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            else
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\r6uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 
            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(1);

            excelworksheet.Cells.NumberFormat = "@";

            if (cbRegions.SelectedIndex >= 0)
                excelworksheet.Cells[4, 3] = cbRegions.Text;

            for (int i = 0; i < dgvReport.Rows.Count; i++)
            {
                for (int j = 0; j < dgvReport.Columns.Count; j++)
                {
                    excelworksheet.Cells[i + 7, dgvReport.Columns[j].DisplayIndex + 3] = dgvReport[j, i].FormattedValue;
                    addBorder(excelworksheet.Cells[i + 7, dgvReport.Columns[j].DisplayIndex + 3]);
                }
                excelworksheet.Cells[i + 7, 2] = i + 1;
            }
            excelworksheet.Columns.AutoFit();
        }

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        private void dgvReport_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex.Equals(2) && e.Value == null)
            {
                e.Value = 0;
            }
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvReport.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvReport);
        }


    }
}
